/***
This figure compares seven-day moving averages of total consumer spending (from 
Affinity Solutions data) and small business revenue (from Womply data) for the 
period January 1 2019 to June 30 2020 (changed to Jan 1 2020 to Oct 31 2021). 
The first figure is for Retail Services and the second for Food Services and 
Accomodation. 
***/ 

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
project, uses("${root}/code/set_globals.do")
include "${root}/code/set_globals.do"
local category "Spending"

* Create required subfolders
cap mkdir "${root}/results/`category'"
cap mkdir "${root}/results/paper numbers"
cap mkdir "${root}/results/paper numbers/`category'"

* Erase output numbers
cap erase "${root}/results/paper numbers/`category'/Small Business Revenue Changes vs Consumer Spending Changes - Retail.yaml"
cap erase "${root}/results/paper numbers/`category'/Small Business Revenue Changes vs Consumer Spending Changes - Food.yaml"

*-------------------------------------------------------------------------------
* Load and process data
*-------------------------------------------------------------------------------

project, uses("${root}/data/web/data/Womply - National - Weekly.csv")
import delimited "${root}/data/web/data/Womply - National - Weekly.csv", clear

gen date_endofweek = mdy(month, day_endofweek, year)
format date_endofweek %td 
keep if year >= 2019
compress

keep date_endofweek revenue_*

greshape long revenue_ , i(date_endofweek) j(sector) string

rename revenue_ revenue

gen naics = . 
replace naics = 44 if sector == "retail"
replace naics = 72 if sector == "food_accommodation"

drop if naics == .

tempfile womply
save `womply'

project, uses("${root}/data/web/data/Affinity - National - Daily.csv")
import delimited "${root}/data/web/data/Affinity - National - Daily.csv", clear 

keep year-day spend_retail_no_grocery spend_acf

* Create date variable 
gen date = mdy(month, day, year)
format date %td

keep if year(date) >= 2020

rename date date_endofweek

* Reshape
greshape long spend, i(date_endofweek) j(MC_cat) string

replace MC_cat = "AFS_MC" if MC_cat == "_acf" 
replace MC_cat = "44W" if MC_cat == "_retail_no_grocery"

rename spend spending_snorm

gen naics = 72 if MC_cat=="AFS_MC"  
replace naics = 44 if MC_cat=="44W"  
drop if mi(naics) 
replace spending_snorm = spending_snorm + 1 

* Merge
merge m:1 date_endofweek naics using `womply', nogen keep(3)
gen ym = ym(year,month)
format ym %tm

* Cut at final data
drop if date_endofweek > ${finaldate}

* Rescale 
replace spending_snorm = 100 * (spending_snorm - 1) 
replace revenue = 100 * (revenue) 

* Create error terms 
gen error = spending_snorm - revenue
gen error_sq = error ^ 2

* Plots
foreach ind in 44 72 {
	
	sum error_sq if naics == `ind'
	local rmse : di %4.2f sqrt(`r(mean)')
	if "`ind'" == "44" local ypos = 60  
	if "`ind'" == "72" local ypos = 20

	if "`ind'" == "72"{
		local title "food_drink"
		local pos = 5
		local ylab ylab(-80 "-80%" -60 "-60%" -40 "-40%" -20 "-20%" 0 "0%" 20 "20%", nogrid)
		local industry "AFS"
	}
	if "`ind'" == "44" {
		local title "retail_no_auto"
		local pos = 5
		local ylab "ylab(0.6(0.2)1.8)"
		local ylab ylab(-40 "-40%" -20 "-20%" 0 "0%" 20 "20%" 40 "40%" 60 "60%", nogrid)
		local industry "Retail"
	}
	format date_endofweek %td
	
	tw ///
		(line spending_snorm date_endofweek if naics == `ind') ///
		(line revenue date_endofweek if naics == `ind' ), ///
		text(`ypos' `=mdy(12, 1, 2021)' "RMSE: `rmse' p.p.", size(small) color(gs8)) /// 
		ytitle("Change Relative to January (%)") ///
		xtitle("") /// 
		${title_`version'} ylabel(, nogrid) xlabel( `=mdy(1, 1, 2020)' `" "Jan" "2020" "' ///
		 `=mdy(4, 1, 2020)'  "Apr"  ///
		 `=mdy(7, 1, 2020)'  "Jul"  ///
		 `=mdy(10, 1, 2020)'  "Oct" ///
		 `=mdy(1, 1, 2021)' `" "Jan" "2021" "' ///
		 `=mdy(4, 1, 2021)'  "Apr"  ///
		 `=mdy(7, 1, 2021)' "Jul" ///
		 `=mdy(10, 1, 2021)'  "Oct" ///
		, labsize(small)) ///
		`ylab' /// 
		legend(pos(`pos') ring(0) size(small) cols(1) ///
		order(1 "Spending (Affinity Solutions)" 2 "Small Business Revenue (Womply)"))
	oi_graph_export "${root}/results/Spending/Consumer Spending vs Small Business Revenue Changes `industry' - new data", type(${fig_type})
}

* Make monthly changes figures
gcollapse (mean) spending_snorm revenue, by(naics year month)

gen date = ym(year, month)

bysort naics (date): gen change_spending_snorm = spending_snorm - spending_snorm[_n-1]
bysort naics (date): gen change_revenue = revenue - revenue[_n-1]

* Create error terms 
gen error = change_spending_snorm - change_revenue
gen error_sq = error ^ 2

* Correlations
corr change_spending_snorm change_revenue if naics == 44
local corr_44: di %4.2f `r(rho)'

corr change_spending_snorm change_revenue if naics == 72
local corr_72: di %4.2f `r(rho)'

* Plots
foreach ind in 44 72 {
	
	sum error_sq if naics == `ind'
	local rmse : di %4.2f sqrt(`r(mean)')
	if "`ind'" == "44" local ypos = -38  
	if "`ind'" == "72" local ypos = -38

	if "`ind'" == "72"{
		local title "food_drink"
		local pos = 1
		local ylab ylab(-40 "-40 p.p." -20 "-20 p.p." 0 "0 p.p." 20 "+20 p.p.", nogrid)
		local industry "AFS"
		local sector "Food"
		local key "womply_aff_food_rmse"
	}
	if "`ind'" == "44" {
		local title "retail_no_auto"
		local pos = 1
		local ylab ylab(-40 "-40 p.p." -20 "-20 p.p." 0 "0 p.p." 20 "+20 p.p." 40 "+40 p.p.", nogrid)
		local industry "Retail"
		local sector "Retail"
		local key "womply_aff_ret_rmse"
	}
	format date %tm
	
	tw ///
		(line change_spending_snorm date if naics == `ind', color(oi2)) ///
		(line change_revenue date if naics == `ind', color(oi3)), ///
		text(`ypos' `=ym(2021, 10) + 0.5' "Correlation: `corr_`ind''" "RMSE: `rmse' p.p.", size(small) color(gs8) justification(left)) /// 
		ytitle("Month-on-Month Change (p.p.)") ///
		xtitle("") /// 
		${title_`version'} ylabel(, nogrid) xlabel( `=ym(2020, 1)' `" "Jan" "2020" "' ///
		 `=ym(2020, 4)'  "Apr"  ///
		 `=ym(2020, 7)'  "Jul"  ///
		 `=ym(2020, 10)'  "Oct" ///
		 `=ym(2021, 1)' `" "Jan" "2021" "' ///
		 `=ym(2021, 4)'  "Apr"  ///
		 `=ym(2021, 7)' "Jul" ///
		 `=ym(2021, 10)'  "Oct" ///
		, labsize(small)) ///
		`ylab' /// 
		legend(pos(`pos') ring(0) size(small) cols(1) ///
		order(1 "Spending (Affinity Solutions)" 2 "Small Business Revenue (Womply)"))
	oi_graph_export "${root}/results/Spending/Consumer Spending vs Small Business Revenue Changes `industry' - new data - changes", type(${fig_type})
	
	*-------------------------------------------------------------------------------
	* Export output numbers to csv file
	yamlout using "${root}/results/paper numbers/`category'/Small Business Revenue Changes vs Consumer Spending Changes - `sector'.yaml", ///
		key("`key'") ///
		comment("RMSE (p.p.)") ///
		value(`rmse') fmt(%9.2f)

	project, creates("${root}/results/paper numbers/`category'/Small Business Revenue Changes vs Consumer Spending Changes - `sector'.yaml")
}

*-------------------------------------------------------------------------------
* Elasticity: changes in spending vs. changes in small business revenue
*-------------------------------------------------------------------------------

project, uses("${root}/data/web/data/Womply - County - Weekly.csv")
import delimited "${root}/data/web/data/Womply - County - Weekly.csv", clear
gcollapse revenue_all, by(year month countyfips)

tempfile womply
save `womply'

project, uses("${root}/data/web/data/Affinity - County - Daily.csv")
import delimited "${root}/data/web/data/Affinity - County - Daily.csv", clear 

gcollapse spend_all, by(year month countyfips)

* Merge
merge 1:1 year month countyfips using `womply', keep(3) nogen                         

* Merge pop 
rename countyfips county_fips
project, uses("${root}/data/derived/ACS 2014-2018 5-Year County/ACS 2014-2018 County.dta")
merge m:1 county_fips using "${root}/data/derived/ACS 2014-2018 5-Year County/ACS 2014-2018 County.dta", keepusing(pop_2014_2018_est) keep(3) nogen 
rename county_fips countyfips
rename pop_2014_2018_est pop_2018

* Elasticities
forval month = 3/12 {
	di " "
	di `month'
	reg revenue_all spend_all [w = pop_2018] if year == 2020 & month == `month', r
	local beta_2020_`month' = _b[spend_all]
}

forval month = 1/12 {
	di " "
	di `month'
	reg revenue_all spend_all [w = pop_2018] if year == 2021 & month == `month', r
	local beta_2021_`month' = _b[spend_all]
}

clear 
gen year = . 
gen month = . 
gen beta = .

forval month = 3/12 {
	local new_obs = _N + 1
	set obs `new_obs'
	
	replace year = 2020 in `new_obs'
	replace month = `month' in `new_obs'
	replace beta = `beta_2020_`month'' in `new_obs'
}

forval month = 1/12 {
	local new_obs = _N + 1
	set obs `new_obs'
	
	replace year = 2021 in `new_obs'
	replace month = `month' in `new_obs'
	replace beta = `beta_2021_`month'' in `new_obs'
}

gen date = ym(year, month)
format %tm date 

line beta date, ///
xlabel(`=ym(2020, 3)' `""Mar" "2020""'  ///
	   `=ym(2020, 5)' "May"  ///
	   `=ym(2020, 7)' "Jul"  ///
	   `=ym(2020, 9)' "Sep"  `=ym(2020, 11)' "Nov" ///
	   `=ym(2021, 1)' `""Jan" "2021""'  ///
	   `=ym(2021, 3)' "Mar"  `=ym(2021, 5)' "May" ///
	   `=ym(2021, 7)' "Jul"  ///
	   `=ym(2021, 9)' "Sep"  `=ym(2021, 11)' "Nov", labsize(medsmall)) ///
ylabel(, format(%02.1f) nogrid) ///
ytitle("Change in Small Business Revenue" "vs Change in Spending Slope") xtitle(" ")

* Note: the stat cited in the referee response letter is the coefficient in May 2020 for this section (collapse to monthly level)
sum beta if year == 2020 & month == 5 
local rev_spend_elasticity_may2020: di %03.2f r(mean)
di `rev_spend_elasticity_may2020'

*-------------------------------------------------------------------------------
* Output numbers for paper
*-------------------------------------------------------------------------------

cap erase "${root}/results/paper numbers/`category'/Small Business Revenue Changes vs Consumer Spending Changes - Elasticity.yaml"

yamlout using "${root}/results/paper numbers/`category'/Small Business Revenue Changes vs Consumer Spending Changes - Elasticity.yaml", ///
		key("rev_spend_elasticity_may2020") ///
		comment("Elasticity") ///
		value(`rev_spend_elasticity_may2020') fmt(%03.2f)

project, creates("${root}/results/paper numbers/`category'/Small Business Revenue Changes vs Consumer Spending Changes - Elasticity.yaml")
